package com.x.dao;

import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.hibernate.Criteria;
import org.hibernate.criterion.Projections;
import org.springframework.stereotype.Component;

import com.x.model.Configuration;
import com.x.model.Log;

@Component
public class LogDAO extends BaseDAO {
	private String whereList = "shipments";
	private String whereDfa = "CASE WHEN tt <= 24 AND (tt + padding) > 24 THEN shipments ELSE 0 END";
	private String whereFt = "CASE WHEN tt = 48 AND (tt + padding) > 48 THEN shipments ELSE 0 END";

	private Object getShipments() {
		Criteria c = this.getSession().createCriteria(Configuration.class);
		c.setProjection(Projections.sum("shipments"));
		return c.uniqueResult();
	}

	private List<Map<String, Object>> formatResult(List<Object[]> results,
			String name) {
		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
		List<Map<String, Object>> list = new ArrayList<>();
		for (Object[] result : results) {

			BigDecimal impact = new BigDecimal(result[1].toString());
			String value = impact.multiply(new BigDecimal(100)).setScale(4, BigDecimal.ROUND_HALF_UP).toString();
			int fc = 0;
			for (Map<String, Object> map : list) {
				if (map.get(name).equals(result[0].toString())) {
					List<Map<String, String>> infos = (List<Map<String, String>>) map
							.get("info");
					Map<String, String> info = new HashMap<>();
					info.put("date", format.format(result[2]));
					info.put("value", value);
					infos.add(info);
					map.put("info", infos);
					list.remove(map);
					list.add(map);
					fc = 1;
				}
			}
			if (fc == 0) {
				Map<String, Object> map = new HashMap<>();
				map.put(name, result[0].toString());
				List<Map<String, String>> infos = new ArrayList<>();
				Map<String, String> info = new HashMap<>();
				info.put("date", format.format(result[2]));
				info.put("value", value);
				infos.add(info);
				map.put("info", infos);
				list.add(map);
			}

		}
		return list;
	}

	private List<Map<String, Object>> fc(String start, String end, String where) {
		String sql = "select fc,sum("
				+ where
				+ ") as count,paddingdate from ("
				+ "SELECT c.fc, c.shipMethod, c.province, c.city, c.district, c.shipments, c.tt, o.paddingDate, o.padding, o.time "
				+ "FROM configuration c, ("
				+ " SELECT c.fc, c.shipMethod, c.province, c.city, c.district, l.paddingDate, p.padding, "
				+ "max(c.createtime) AS time, max(l.priority) AS LEVEL "
				+ "FROM configuration c, log l, padding p "
				+ "WHERE p.status = 2 and c.createtime <= l.paddingDate AND l.paddingID = p.id "
				+ "AND ((p.fc <> 'all' AND c.fc = p.fc) OR (p.fc = 'all')) "
				+ "AND (( p.shipMethod <> 'all' AND c.shipMethod = p.shipMethod ) OR (p.shipMethod = 'all')) "
				+ "AND (( p.province <> 'all' AND c.province = p.province ) OR (p.province = 'all')) "
				+ "AND (( p.city <> 'all' AND c.city = p.city ) OR (p.city = 'all')) "
				+ "AND (( p.district <> 'all' AND c.district = p.district ) OR (p.district = 'all')) "
				+ "GROUP BY c.fc, c.shipMethod, c.province, c.city, c.district, l.paddingDate ) o "
				+ "WHERE c.fc = o.fc AND c.shipMethod = o.shipMethod AND c.province = o.province "
				+ "AND c.city = o.city AND c.district = o.district AND c.createtime = o.time) a "
				+ "where paddingDate>='" + start + "' and paddingDate<'" + end
				+ "' group by fc,paddingdate";
		List<Object[]> results = this.getSession().createSQLQuery(sql).list();
		return formatResult(results, "fc");
	}

	public List<Map<String, Object>> fcList(String start, String end) {
		return fc(start, end, whereList);
	}

	public List<Map<String, Object>> fcDfa(String start, String end) {
		return fc(start, end, whereDfa);
	}

	public List<Map<String, Object>> fcFt(String start, String end) {
		return fc(start, end, whereFt);
	}

	private List<Map<String, Object>> carrier(String start, String end,
			String where) {
		String sql = "select substring_index(shipMethod,'_',1) as carrier,sum("
				+ where
				+ ") as count,paddingdate from ("
				+ "SELECT c.fc, c.shipMethod, c.province, c.city, c.district, c.shipments, c.tt, o.paddingDate, o.padding, o.time "
				+ "FROM configuration c, ("
				+ " SELECT c.fc, c.shipMethod, c.province, c.city, c.district, l.paddingDate, p.padding, "
				+ "max(c.createtime) AS time, max(l.priority) AS LEVEL "
				+ "FROM configuration c, log l, padding p "
				+ "WHERE p.status = 2 and c.createtime <= l.paddingDate AND l.paddingID = p.id "
				+ "AND ((p.fc <> 'all' AND c.fc = p.fc) OR (p.fc = 'all')) "
				+ "AND (( p.shipMethod <> 'all' AND c.shipMethod = p.shipMethod ) OR (p.shipMethod = 'all')) "
				+ "AND (( p.province <> 'all' AND c.province = p.province ) OR (p.province = 'all')) "
				+ "AND (( p.city <> 'all' AND c.city = p.city ) OR (p.city = 'all')) "
				+ "AND (( p.district <> 'all' AND c.district = p.district ) OR (p.district = 'all')) "
				+ "GROUP BY c.fc, c.shipMethod, c.province, c.city, c.district, l.paddingDate ) o "
				+ "WHERE c.fc = o.fc AND c.shipMethod = o.shipMethod AND c.province = o.province "
				+ "AND c.city = o.city AND c.district = o.district AND c.createtime = o.time) a "
				+ "where paddingDate>='" + start + "' and paddingDate<'" + end
				+ "' group by substring_index(shipMethod,'_',1),paddingdate";
		List<Object[]> results = this.getSession().createSQLQuery(sql).list();
		return formatResult(results, "carrier");
	}

	public List<Map<String, Object>> carrierList(String start, String end) {
		return carrier(start, end, whereList);
	}

	public List<Map<String, Object>> carrierDfa(String start, String end) {
		return carrier(start, end, whereDfa);
	}

	public List<Map<String, Object>> carrierFt(String start, String end) {
		return carrier(start, end, whereFt);
	}

	private List<Map<String, Object>> province(String start, String end,
			String where) {
		String sql = "select province,sum("
				+ where
				+ "),paddingdate,tt,padding from ("
				+ "SELECT c.fc, c.shipMethod, c.province, c.city, c.district, c.shipments, c.tt, o.paddingDate, o.padding, o.time "
				+ "FROM configuration c, ("
				+ " SELECT c.fc, c.shipMethod, c.province, c.city, c.district, l.paddingDate, p.padding, "
				+ "max(c.createtime) AS time, max(l.priority) AS LEVEL "
				+ "FROM configuration c, log l, padding p "
				+ "WHERE p.status = 2 and c.createtime <= l.paddingDate AND l.paddingID = p.id "
				+ "AND ((p.fc <> 'all' AND c.fc = p.fc) OR (p.fc = 'all')) "
				+ "AND (( p.shipMethod <> 'all' AND c.shipMethod = p.shipMethod ) OR (p.shipMethod = 'all')) "
				+ "AND (( p.province <> 'all' AND c.province = p.province ) OR (p.province = 'all')) "
				+ "AND (( p.city <> 'all' AND c.city = p.city ) OR (p.city = 'all')) "
				+ "AND (( p.district <> 'all' AND c.district = p.district ) OR (p.district = 'all')) "
				+ "GROUP BY c.fc, c.shipMethod, c.province, c.city, c.district, l.paddingDate ) o "
				+ "WHERE c.fc = o.fc AND c.shipMethod = o.shipMethod AND c.province = o.province "
				+ "AND c.city = o.city AND c.district = o.district AND c.createtime = o.time) a "
				+ "where paddingDate>='" + start + "' and paddingDate<'" + end
				+ "' group by province,paddingdate";
		List<Object[]> results = this.getSession().createSQLQuery(sql).list();
		return formatResult(results, "location");
	}

	public List<Map<String, Object>> provinceList(String start, String end) {
		return province(start, end, whereList);
	}

	public List<Map<String, Object>> provinceDfa(String start, String end) {
		return province(start, end, whereDfa);
	}

	public List<Map<String, Object>> provinceFt(String start, String end) {
		return province(start, end, whereFt);
	}

	private List<Map<String, Object>> city(String start, String end,
			String province, String where) {
		String sql = "select CONCAT(province,city) as location,sum("
				+ where
				+ ") as count,paddingdate from ("
				+ "SELECT c.fc, c.shipMethod, c.province, c.city, c.district, c.shipments, c.tt, o.paddingDate, o.padding, o.time "
				+ "FROM configuration c, ("
				+ " SELECT c.fc, c.shipMethod, c.province, c.city, c.district, l.paddingDate, p.padding, "
				+ "max(c.createtime) AS time, max(l.priority) AS LEVEL "
				+ "FROM configuration c, log l, padding p "
				+ "WHERE p.status = 2 and c.createtime <= l.paddingDate AND l.paddingID = p.id "
				+ "AND ((p.fc <> 'all' AND c.fc = p.fc) OR (p.fc = 'all')) "
				+ "AND (( p.shipMethod <> 'all' AND c.shipMethod = p.shipMethod ) OR (p.shipMethod = 'all')) "
				+ "AND (( p.province <> 'all' AND c.province = p.province ) OR (p.province = 'all')) "
				+ "AND (( p.city <> 'all' AND c.city = p.city ) OR (p.city = 'all')) "
				+ "AND (( p.district <> 'all' AND c.district = p.district ) OR (p.district = 'all')) "
				+ "GROUP BY c.fc, c.shipMethod, c.province, c.city, c.district, l.paddingDate ) o "
				+ "WHERE c.fc = o.fc AND c.shipMethod = o.shipMethod AND c.province = o.province "
				+ "AND c.city = o.city AND c.district = o.district AND c.createtime = o.time) a "
				+ "where paddingDate>='" + start + "' and paddingDate<'" + end
				+ "' group by CONCAT(province,city),paddingDate";
		List<Object[]> results = this.getSession().createSQLQuery(sql).list();
		return formatResult(results, "location");
	}

	public List<Map<String, Object>> cityList(String start, String end,
			String province) {
		return city(start, end, province, whereList);
	}

	public List<Map<String, Object>> cityDfa(String start, String end,
			String province) {
		return city(start, end, province, whereDfa);
	}

	public List<Map<String, Object>> cityFt(String start, String end,
			String province) {
		return city(start, end, province, whereFt);
	}

	private List<Map<String, Object>> district(String start, String end,
			String province, String city, String where) {
		String sql = "select CONCAT(province,city,district) as location,sum("
				+ where
				+ ") as count,paddingdate from ("
				+ "SELECT c.fc, c.shipMethod, c.province, c.city, c.district, c.shipments, c.tt, o.paddingDate, o.padding, o.time "
				+ "FROM configuration c, ("
				+ " SELECT c.fc, c.shipMethod, c.province, c.city, c.district, l.paddingDate, p.padding, "
				+ "max(c.createtime) AS time, max(l.priority) AS LEVEL "
				+ "FROM configuration c, log l, padding p "
				+ "WHERE p.status = 2 and c.createtime <= l.paddingDate AND l.paddingID = p.id "
				+ "AND ((p.fc <> 'all' AND c.fc = p.fc) OR (p.fc = 'all')) "
				+ "AND (( p.shipMethod <> 'all' AND c.shipMethod = p.shipMethod ) OR (p.shipMethod = 'all')) "
				+ "AND (( p.province <> 'all' AND c.province = p.province ) OR (p.province = 'all')) "
				+ "AND (( p.city <> 'all' AND c.city = p.city ) OR (p.city = 'all')) "
				+ "AND (( p.district <> 'all' AND c.district = p.district ) OR (p.district = 'all')) "
				+ "GROUP BY c.fc, c.shipMethod, c.province, c.city, c.district, l.paddingDate ) o "
				+ "WHERE c.fc = o.fc AND c.shipMethod = o.shipMethod AND c.province = o.province "
				+ "AND c.city = o.city AND c.district = o.district AND c.createtime = o.time) a "
				+ "where paddingDate>='" + start + "' and paddingDate<'" + end
				+ "' group by CONCAT(province,city,district),paddingDate";
		List<Object[]> results = this.getSession().createSQLQuery(sql).list();
		return formatResult(results, "location");
	}

	public List<Map<String, Object>> districtList(String start, String end,
			String province, String city) {
		return district(start, end, province, city, whereList);
	}

	public List<Map<String, Object>> districtDfa(String start, String end,
			String province, String city) {
		return district(start, end, province, city, whereDfa);
	}

	public List<Map<String, Object>> districtFt(String start, String end,
			String province, String city) {
		return district(start, end, province, city, whereFt);
	}

	public void save(Log log) {
		this.getSession().merge(log);

	}

	public void deleteByPaddingID(String id) {
		String deleteChildrenSql = "delete from Log log where log.paddingID = :paddingID";
		this.getSession().createQuery(deleteChildrenSql)
				.setParameter("paddingID", id).executeUpdate();
	}

	private List<Map<String, String>> formatDayResult(List<Object[]> results,
			String type) {
		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
		Object sum = getShipments();
		List<Map<String, String>> infos = new ArrayList<>();
		for (Object[] result : results) {

			BigDecimal impact = new BigDecimal(result[0].toString());
			String value = impact.multiply(new BigDecimal(100)).setScale(4, BigDecimal.ROUND_HALF_UP).toString();

			Map<String, String> info = new HashMap<>();
			if (type == "day") {
				info.put("date", format.format(result[1]));
			} else {
				info.put("date", result[1].toString());
			}

			info.put("value", value);
			infos.add(info);
		}
		return infos;
	}

	private List<Map<String, String>> summaryDayQuery(String start, String end,
			String where) {
		String sql = "SELECT sum("
				+ where
				+ ") as count, paddingDate FROM ("
				+ "SELECT c.fc, c.shipMethod, c.province, c.city, c.district, c.shipments, c.tt, o.paddingDate, o.padding, o.time "
				+ "FROM configuration c, ("
				+ " SELECT c.fc, c.shipMethod, c.province, c.city, c.district, l.paddingDate, p.padding, "
				+ "max(c.createtime) AS time, max(l.priority) AS LEVEL "
				+ "FROM configuration c, log l, padding p "
				+ "WHERE p.status = 2 and c.createtime <= l.paddingDate AND l.paddingID = p.id "
				+ "AND ((p.fc <> 'all' AND c.fc = p.fc) OR (p.fc = 'all')) "
				+ "AND (( p.shipMethod <> 'all' AND c.shipMethod = p.shipMethod ) OR (p.shipMethod = 'all')) "
				+ "AND (( p.province <> 'all' AND c.province = p.province ) OR (p.province = 'all')) "
				+ "AND (( p.city <> 'all' AND c.city = p.city ) OR (p.city = 'all')) "
				+ "AND (( p.district <> 'all' AND c.district = p.district ) OR (p.district = 'all')) "
				+ "GROUP BY c.fc, c.shipMethod, c.province, c.city, c.district, l.paddingDate ) o "
				+ "WHERE c.fc = o.fc AND c.shipMethod = o.shipMethod AND c.province = o.province "
				+ "AND c.city = o.city AND c.district = o.district AND c.createtime = o.time) a "
				+ "where paddingDate>='" + start + "' and paddingDate<'" + end
				+ "' group by paddingdate";
		List<Object[]> results = this.getSession().createSQLQuery(sql).list();
		return formatDayResult(results, "day");
	}

	public List<List<Map<String, String>>> summaryDay(String start, String end) {
		List<List<Map<String, String>>> results = new ArrayList<>();
		List<Map<String, String>> resultList = summaryDayQuery(start, end,
				whereList);
		results.add(resultList);
		List<Map<String, String>> resultDFA = summaryDayQuery(start, end,
				whereDfa);
		results.add(resultDFA);
		List<Map<String, String>> resultFT = summaryDayQuery(start, end,
				whereFt);
		results.add(resultFT);
		return results;
	}

	public List<List<Map<String, String>>> summaryWeek(String start, String end) {
		List<List<Map<String, String>>> results = new ArrayList<>();
		List<Map<String, String>> resultList = summaryWeekQuery(start, end,
				whereList);
		results.add(resultList);
		List<Map<String, String>> resultDFA = summaryWeekQuery(start, end,
				whereDfa);
		results.add(resultDFA);
		List<Map<String, String>> resultFT = summaryWeekQuery(start, end,
				whereFt);
		results.add(resultFT);
		return results;
	}

	private List<Map<String, String>> summaryWeekQuery(String start,
			String end, String where) {
		String sql = "SELECT sum("
				+ where
				+ ")/7 as count, CONCAT('Week ',week(paddingDate),' of ',year(paddingDate)) as date FROM ("
				+ "SELECT c.fc, c.shipMethod, c.province, c.city, c.district, c.shipments, c.tt, o.paddingDate, o.padding, o.time "
				+ "FROM configuration c, ("
				+ " SELECT c.fc, c.shipMethod, c.province, c.city, c.district, l.paddingDate, p.padding, "
				+ "max(c.createtime) AS time, max(l.priority) AS LEVEL "
				+ "FROM configuration c, log l, padding p "
				+ "WHERE p.status = 2 and c.createtime <= l.paddingDate AND l.paddingID = p.id "
				+ "AND ((p.fc <> 'all' AND c.fc = p.fc) OR (p.fc = 'all')) "
				+ "AND (( p.shipMethod <> 'all' AND c.shipMethod = p.shipMethod ) OR (p.shipMethod = 'all')) "
				+ "AND (( p.province <> 'all' AND c.province = p.province ) OR (p.province = 'all')) "
				+ "AND (( p.city <> 'all' AND c.city = p.city ) OR (p.city = 'all')) "
				+ "AND (( p.district <> 'all' AND c.district = p.district ) OR (p.district = 'all')) "
				+ "GROUP BY c.fc, c.shipMethod, c.province, c.city, c.district, l.paddingDate ) o "
				+ "WHERE c.fc = o.fc AND c.shipMethod = o.shipMethod AND c.province = o.province "
				+ "AND c.city = o.city AND c.district = o.district AND c.createtime = o.time) a "
				+ "where paddingDate>='" + start + "' and paddingDate<'" + end
				+ "' group by CONCAT('Week ',week(paddingDate),' of ',year(paddingDate))";
		List<Object[]> results = this.getSession().createSQLQuery(sql).list();
		return formatDayResult(results, "week");
	}

	public List<List<Map<String, String>>> summaryMonth(String start, String end) {
		List<List<Map<String, String>>> results = new ArrayList<>();
		List<Map<String, String>> resultList = summaryMonthQuery(start, end,
				whereList);
		results.add(resultList);
		List<Map<String, String>> resultDFA = summaryMonthQuery(start, end,
				whereDfa);
		results.add(resultDFA);
		List<Map<String, String>> resultFT = summaryMonthQuery(start, end,
				whereFt);
		results.add(resultFT);
		return results;
	}

	private List<Map<String, String>> summaryMonthQuery(String start,
			String end, String where) {
		String sql = "SELECT sum("
				+ where
				+ ")/day(last_day(paddingDate)) as count, CONCAT(MonthName(paddingDate),' ',year(paddingDate)) as date FROM ("
				+ "SELECT c.fc, c.shipMethod, c.province, c.city, c.district, c.shipments, c.tt, o.paddingDate, o.padding, o.time "
				+ "FROM configuration c, ("
				+ " SELECT c.fc, c.shipMethod, c.province, c.city, c.district, l.paddingDate, p.padding, "
				+ "max(c.createtime) AS time, max(l.priority) AS LEVEL "
				+ "FROM configuration c, log l, padding p "
				+ "WHERE p.status = 2 and c.createtime <= l.paddingDate AND l.paddingID = p.id "
				+ "AND ((p.fc <> 'all' AND c.fc = p.fc) OR (p.fc = 'all')) "
				+ "AND (( p.shipMethod <> 'all' AND c.shipMethod = p.shipMethod ) OR (p.shipMethod = 'all')) "
				+ "AND (( p.province <> 'all' AND c.province = p.province ) OR (p.province = 'all')) "
				+ "AND (( p.city <> 'all' AND c.city = p.city ) OR (p.city = 'all')) "
				+ "AND (( p.district <> 'all' AND c.district = p.district ) OR (p.district = 'all')) "
				+ "GROUP BY c.fc, c.shipMethod, c.province, c.city, c.district, l.paddingDate ) o "
				+ "WHERE c.fc = o.fc AND c.shipMethod = o.shipMethod AND c.province = o.province "
				+ "AND c.city = o.city AND c.district = o.district AND c.createtime = o.time) a "
				+ "where paddingDate>='" + start + "' and paddingDate<'" + end
				+ "' group by CONCAT(MonthName(paddingDate),' ',year(paddingDate))";
		List<Object[]> results = this.getSession().createSQLQuery(sql).list();
		return formatDayResult(results, "month");
	}

}
